package com.fiap.gestaoPassagens.venda;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.fiap.gestaoPassagens.assento.AssentoBean;
import com.fiap.gestaoPassagens.cliente.ClienteBean;
import com.fiap.gestaoPassagens.dao.ConnectionFactory;
import com.fiap.gestaoPassagens.voo.VooBean;



public class VendaDao implements Serializable{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public ConfigValoresBean updateConfigValores(ConfigValoresBean c) throws SQLException{
			Connection con = ConnectionFactory.getConnection();
			PreparedStatement pstmt = null;
			StringBuffer sql = new StringBuffer();

			int i=0;
			try {
				sql.append(" UPDATE valores ");
				sql.append(" SET ");
				sql.append(" vl_assento_classe_economica  = ?, ");
				sql.append(" vl_assento_primeira_classe   = ?, ");
				sql.append(" vl_taxa_alteracao_reserva    = ? ");
				
				
				pstmt = con.prepareStatement(sql.toString());
				
				pstmt.setBigDecimal(++i, c.getValorAssentoClasseEconomica());
				pstmt.setBigDecimal(++i, c.getValorAssentoPrimeiraClasse());
				pstmt.setBigDecimal(++i, c.getValorTaxaAlteracaoReserva());
				
				
				pstmt.executeUpdate();

			} catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				pstmt.close();
				con.close();
			}
			return c;
		}
	
	public ConfigValoresBean getConfigValores() throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		ConfigValoresBean c = new ConfigValoresBean();

		try {
			sql.append(" SELECT * FROM  VALORES ");
			
			pstmt = con.prepareStatement(sql.toString());
									
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				c.setValorAssentoClasseEconomica(rs.getBigDecimal("vl_assento_classe_economica"));
				c.setValorAssentoPrimeiraClasse(rs.getBigDecimal("vl_assento_primeira_classe"));
				c.setValorTaxaAlteracaoReserva(rs.getBigDecimal("vl_taxa_alteracao_reserva"));
			}
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
		return c;
	}
	
	public ReservaBean updateReserva(ReservaBean reserva) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		int i = 0;

		try {

			sql.append(" UPDATE db_fiap.reserva ");
			sql.append(" SET ");
			
			if(reserva.getDataPagamento()!=null){
				sql.append(" DATA_PAGTO = ?, ");	
			}

			sql.append(" STATUS_RESERVA = ? ");
			sql.append(" WHERE ID_RESPONSAVEL_PAGTO = ? ");

			pstmt = con.prepareStatement(sql.toString());

			if(reserva.getDataPagamento()!=null){
				pstmt.setTimestamp(++i, new Timestamp(new Date().getTime()));
			}	
			pstmt.setLong(++i, reserva.getStatusReserva());
			pstmt.setLong(++i, reserva.getResponsavelPagto().getId());

			pstmt.executeUpdate();

			return reserva;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			pstmt.close();
			con.close();
		}
	}

	public ReservaBean createReserva(ReservaBean reserva) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs=null;

		try {
			
			sql.append(" INSERT INTO db_fiap.reserva ");
			sql.append(" (ID_RESPONSAVEL_PAGTO, ");
			sql.append(" DATA_RESERVA, ");
			sql.append(" STATUS_RESERVA) ");
			sql.append(" VALUES (?,?,?) ");

			
			pstmt = con.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);

				int i=0;
				
				pstmt.setLong(++i, reserva.getResponsavelPagto().getId());
				pstmt.setTimestamp(++i,new Timestamp(new Date().getTime()));
				pstmt.setLong(++i, ReservaBean.STATUS_RESERVADO);

			pstmt.executeUpdate();	
		
			//Retorna o ID gerado.
			rs=pstmt.getGeneratedKeys();
			if(rs.next()){
				reserva.setId(rs.getLong(1));
			}
			
			return reserva;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
	}

	public ItemReservaBean updateItemReserva(ItemReservaBean itemReserva) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		int i = 0;

		try {

			sql.append(" UPDATE db_fiap.reserva_cliente ");
			sql.append(" SET ");
			sql.append(" ID_RESERVA = ?, ");
			sql.append(" ID_CLIENTE = ?, ");
			sql.append(" ID_VOO = ?, ");
			sql.append(" ID_TRECHO = ?, ");
			sql.append(" CODIGO_ASSENTO = ? ");
			sql.append(" WHERE ID = ?; ");


			pstmt = con.prepareStatement(sql.toString());

			pstmt.setLong(++i, itemReserva.getIdReserva());
			pstmt.setLong(++i, itemReserva.getPassageiro().getId());
			pstmt.setLong(++i, itemReserva.getAssentoComprado().getIdVoo());
			pstmt.setLong(++i, itemReserva.getAssentoComprado().getIdTrecho());
			pstmt.setString(++i, itemReserva.getAssentoComprado().getCodigo());
			pstmt.setLong(++i, itemReserva.getIdItemReserva());

			pstmt.executeUpdate();

			return itemReserva;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			pstmt.close();
			con.close();
		}
	}

	public ItemReservaBean createItemReservaBean(ItemReservaBean itemReserva) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs=null;

		try {
			
			sql.append(" INSERT INTO db_fiap.reserva_cliente ");
			sql.append(" (ID_RESERVA, ");
			sql.append("  ID_CLIENTE, ");
			sql.append("  ID_VOO, ");
			sql.append("  ID_TRECHO, ");
			sql.append("  CODIGO_ASSENTO) ");
			sql.append(" VALUES (?,?,?,?,?); ");


			
			pstmt = con.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);

				int i=0;
				
				pstmt.setLong(++i, itemReserva.getIdReserva());
				pstmt.setLong(++i, itemReserva.getPassageiro().getId());
				pstmt.setLong(++i, itemReserva.getAssentoComprado().getIdVoo());
				pstmt.setLong(++i, itemReserva.getAssentoComprado().getIdTrecho());
				pstmt.setString(++i, itemReserva.getAssentoComprado().getCodigo());

			pstmt.executeUpdate();	
		
			//Retorna o ID gerado.
			rs=pstmt.getGeneratedKeys();
			if(rs.next()){
				itemReserva.setIdItemReserva(rs.getLong(1));
			}
			
			return itemReserva;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
	}
	
	public ItemCobrancaBean createItemCobrancaBean(ItemCobrancaBean itemCobrancaBean) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		try {

			sql.append(" INSERT INTO db_fiap.reserva_cliente_valores ");
			sql.append(" (ID_RESERVA_CLIENTE, ");
			sql.append(" TIPO_COBRANCA, ");
			sql.append(" VALOR_COBRANCA) ");
			sql.append(" VALUES (?,?,?) ");

			int i = 0;

			pstmt = con.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			
			pstmt.setLong(++i,itemCobrancaBean.getIdReservaCliente());
			pstmt.setString(++i, itemCobrancaBean.getDescricaoCobranca());
			pstmt.setBigDecimal(++i, itemCobrancaBean.getValorCobranca());

			pstmt.executeUpdate();


			return itemCobrancaBean;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			pstmt.close();
			con.close();
			
		}
	}
	
	public List<ReservaBean> listReservaBeanByClienteID(long clienteID) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		List<ReservaBean> listaReservas = new ArrayList<ReservaBean>();
		ReservaBean reserva = null;

		try {
			sql.append(" SELECT R.ID, ");
				sql.append(" 	   R.ID_RESPONSAVEL_PAGTO, ");
				sql.append(" 	   R.ID_VOO, ");
				sql.append(" 	   R.STATUS_RESERVA, ");
				sql.append(" 	   R.DATA_PAGTO, ");
				sql.append(" 	   R.DATA_RESERVA  	 ");
				sql.append("   FROM DB_FIAP.RESERVA R ");
				sql.append(" WHERE R.ID_RESPONSAVEL_PAGTO = ? ");
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.setLong(1, clienteID);
									
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				reserva = new ReservaBean();
				reserva.setId(rs.getLong("ID"));
				
				Timestamp dataPagto = rs.getTimestamp("DATA_PAGTO");
				if(dataPagto!=null){
					reserva.setDataPagamento((new Date(dataPagto.getTime())));
				}	
				reserva.setDataReserva((new Date(rs.getTimestamp("DATA_RESERVA").getTime())));
				reserva.setStatusReserva(rs.getInt("STATUS_RESERVA"));
				
				VooBean vooComprado = new VooBean();
				vooComprado.setId(rs.getLong("ID_VOO"));
				reserva.setVooComprado(vooComprado);
				
				listaReservas.add(reserva);
			}
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
		return listaReservas;
	}

	public List<ItemReservaBean> listItemReservaByReservaID(long reservaID) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		List<ItemReservaBean> listaItemReservaBean = new ArrayList<ItemReservaBean>();
		ItemReservaBean itemReservaBean = null;

		try {
			sql.append(" SELECT ");
			sql.append(" ID, ");
			sql.append(" ID_RESERVA, ");
			sql.append(" ID_CLIENTE, ");
			sql.append(" ID_VOO, ");
			sql.append(" ID_TRECHO, ");
			sql.append(" CODIGO_ASSENTO ");
			sql.append(" FROM db_fiap.reserva_cliente ");
			sql.append(" where ID_RESERVA = ? ");

	
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.setLong(1, reservaID);
									
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				itemReservaBean = new ItemReservaBean();
				itemReservaBean.setIdReserva(rs.getLong("ID_RESERVA"));
				itemReservaBean.setIdItemReserva(rs.getLong("ID"));
				
				AssentoBean assento = new AssentoBean();
				assento.setIdVoo(rs.getLong("ID_VOO"));
				assento.setIdTrecho(rs.getLong("ID_TRECHO"));
				assento.setCodigo(rs.getString("CODIGO_ASSENTO"));
				itemReservaBean.setAssentoComprado(assento);
				
				ClienteBean passageiro = new ClienteBean();
				passageiro.setId(rs.getLong("ID_CLIENTE"));
				
				itemReservaBean.setPassageiro(passageiro);
				listaItemReservaBean.add(itemReservaBean);
			}
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
		return listaItemReservaBean;
	}

	public List<ItemCobrancaBean> listItemCobrancaByItemReservaID(long itemReservaID) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		List<ItemCobrancaBean> listaItemCobrancaBean= new ArrayList<ItemCobrancaBean>();
		ItemCobrancaBean itemCobrancaBean = null;

		try {
			sql.append(" SELECT ");
			sql.append(" ID_RESERVA_CLIENTE, ");
			sql.append(" TIPO_COBRANCA, ");
			sql.append(" VALOR_COBRANCA ");
			sql.append(" FROM db_fiap.reserva_cliente_valores ");
			sql.append(" WHERE ID_RESERVA_CLIENTE = ? ");


	
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.setLong(1, itemReservaID);
									
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				itemCobrancaBean = new ItemCobrancaBean();
				itemCobrancaBean.setIdReservaCliente(rs.getLong("ID_RESERVA_CLIENTE"));
				itemCobrancaBean.setDescricaoCobranca(rs.getString("TIPO_COBRANCA"));
				itemCobrancaBean.setValorCobranca(rs.getBigDecimal("VALOR_COBRANCA"));
				
				listaItemCobrancaBean.add(itemCobrancaBean);
			}
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
		return listaItemCobrancaBean;
	}
	
}
